package com.vsoft.sms_cm;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.log4j.Level;
import org.apache.log4j.Logger;


import com.vsoft.framework.core.BaseException;
import com.vsoft.framework.core.BusiObj;
import com.vsoft.framework.core.IService;
import com.vsoft.framework.core.db.OperAgent;
import com.vsoft.framework.core.db.SQLArgs;
import com.vsoft.framework.core.esb.ServiceBus;
import com.vsoft.framework.core.util.BOHelper;
import com.vsoft.framework.core.util.LogCollect;
public class CmPublic  implements IService
{
	private static Logger	vLogger		= Logger.getLogger(CmPublic.class);

	private String				vName			= CmPublic.class.getName();

	private Connection		vConn			= null;

	private SQLArgs				vArg			= new SQLArgs();

	private BusiObj				vInBO		= null;

	private StringBuffer	vStrBuf				= new StringBuffer();

	private String				staffNo			= "";

	private String				staffCode		= "";

	private String				staffName	= "";
	///////////////////////////////////////////////////////////分页变量
	private int pageIndex=1,pageCount=1,pageRows=30;
	private int startRowNum,endRowNum,totalRows;
	boolean flagQueryCount=false;
	///////////////////////////////////////////////////////////
	private static final String		MDListName	= "MD";
	private static final String		MDTableListName	= "TABLES";
	private static final String		MDRelationListName	= "RELATIONS";
	private static final String		MDTN	= "TN";
	private final String		KEYForCondition= "CONDITION";
	private final String		KEYForConditionTable= "RELATIONTABLE";
	private final String		SortColumns="___SORTCOLUMNS";
	private final String		SortColumnsDesc="___SORTCOLUMNSDESC";
	private String				gSql				= "";
	private final String				QueryResultBoListName				= "RESULT";
	private static String	gDateFormat	=	"yyyy-MM-dd HH:mm:ss";
	private static String	gDayFormat	=	"yyyy-MM-dd";
	private final  int  orderSeqLength=10;//订单号数字长度，订单号由日期+数字组成，数字长度不足的，前面补0
	public CmPublic(Connection conn)
	{
		this.vConn = conn;
		
		//vLogger.setLevel(Level.ERROR);
	}
	
/*	public CmPublic()
	{
		//vLogger.setLevel(Level.ERROR);
	}*/
	public BusiObj execute(BusiObj obj) throws BaseException 
	{
		return obj;
	}
	private void prepareForPage()throws BaseException
	{
		//////////////////////////////////////////////////////////////////分页准备
		String pageRow=vInBO.getString("pageRows","");//每页行数
		if(!pageRow.trim().equals(""))
			this.setPageRows(Integer.parseInt(pageRow));
		String total =vInBO.getString("totalRows","");//总行数
		if(!total.trim().equals(""))
		{
			totalRows=Integer.parseInt(total);
			if (totalRows<=0)
				flagQueryCount=true;
		}
		else
			flagQueryCount=true;
			
		String curPageNo=vInBO.getString("curPageNo","");
		if(!curPageNo.trim().equals(""))
			pageIndex =Integer.parseInt(curPageNo);
		else
			pageIndex =1;
		this.setPageRowNum(pageIndex);
		//////////////////////////////////////////////////////////////////
	}
	public BusiObj execute( Object Service,BusiObj obj) throws BaseException
	{
		vInBO 						= 	obj;
		vConn 						= vInBO.getConnection();
		String serv_name 		= vInBO.getName();
		String class_name		=	Service.getClass().getName();
		vLogger.info("==================class_name:"+class_name+" ========serv_name:"+serv_name+" excute" );
		vLogger.info("=================="+vName+"'s bo is " + BOHelper.boToXML(vInBO));

		this.prepareForPage();//分页准备
		//BusiObj session = vInBO.getBusiObj("SESSION_DATA");
		staffNo 					= vInBO.getString("SESSION_STAFF_NO", "");
		staffCode 				= vInBO.getString("SESSION_STAFF_CODE", "");
		staffName 				= vInBO.getString("SESSION_STAFF_NAME", "");
		int	lastDot				=	serv_name.lastIndexOf(".");
		String actionId 			= serv_name.substring(lastDot+1);
		BusiObj retBo			=	null;
		Method method;
		try
		{
			method = Service.getClass().getMethod(actionId, new Class[] { BusiObj.class });
		} 
		catch (SecurityException e)
		{
			throw new BaseException(vName +"-001","调用服务异常：无权访问方法 " + vName + "." + actionId + ": " + e.getMessage());
		}
		catch (NoSuchMethodException e)
		{
			throw new BaseException(vName +"-002","调用服务异常：不存在的方法 " +vName + "." + actionId + ": " + e.getMessage());
		}
		
		
		Integer retCode;
		try 
		{
			retBo = (BusiObj)method.invoke(Service, new Object[] { vInBO });
		}
		catch (IllegalArgumentException e) 
		{
			throw new BaseException(vName +"-003", "调用服务异常：" + e.getMessage());
		}
		catch (IllegalAccessException e) 
		{
			throw new BaseException(vName +"-004","调用服务异常：" + e.getMessage());
		}
		catch (InvocationTargetException e) 
		{
			Throwable te = e.getTargetException();
			if(te instanceof BaseException)
			{
				throw (BaseException)te;
			}
			else
			{
				throw new  BaseException(vName +"-005", "调用服务异常：" + getstackmsg(te));
			}
		}
		String ResultFlag=retBo.getString("RESULTFLAG", "");
		if(ResultFlag.trim().equals(""))
			retBo.setValue("ResultFlag", "1");
		return retBo;
	}
	/*根据主键删除一行记录
	 * 
	 * */
	public void deleteOneRow(BusiObj bo,String tableName,Connection conn,ArrayList<String> aTableKey ) throws BaseException
	{
		String SqlPkCondition				=	this.getSqlPkCondition(bo, aTableKey);//
		//String	SqlPkCondition				=	(String)hSqlPkCondition.get("SQL_WHERE");
		String	sql								=	" Delete From "+tableName+" Where "+SqlPkCondition;
		vLogger.info("=====================delete row by pk    sql:" + sql);
		OperAgent.execUpdate(conn, "delete "+tableName,sql, vArg);
	}
	private String genCondition(HashMap<String,String>h)
	{
		return this.genCondition(h, true);
	}
	/*生成条件操作符，如该条件到底是and还是like（根据%判断）
	 * or
	 * */
	private String genConditionType(String condition,boolean firstCondition)
	{
		String conditionType	=	" ";
		if(firstCondition)
			return conditionType ;
		if(condition.toUpperCase().indexOf("$OR$")>0 )
			conditionType		= " OR ";
		else
			conditionType 	= " AND ";

		return conditionType;
	}
	/*生成条件操作符，如该条件到底是like（根据%判断）还是=
	 * or
	 * */
	private String genConditionOperator(String condition)
	{
		String conditionOperator	=	"";
		
		if(condition.indexOf("%")>=0)
			conditionOperator 		= " LIKE ";
		else
			conditionOperator			=	" = ";

		return conditionOperator;
	}
	/*genCondition(HashMap<String,String>h,boolean flag )
	 * 生成条件语句
	 * 入参：HashMap<String,String>h 条件值对
	 * 			flag，是否返回字符串开始包括where
	 * 如果 hash的key数量=1，则 返回的字符串没有and（这个是单个条件所决定，但是多个条件可能多次调用）
	 * 应该是返回的字符串前面都没有and，而只有中间才有and
	 * 注：在此，打开了vArg
	 * */
	private String genCondition(HashMap<String,String>h,boolean flag )
	{
		String condition						=	"  ";
		if(flag)
			condition=	" where ";
		int colCount								=	0;
		if(h==null || h.isEmpty())
			condition =" ";
		else
		{
			if(flag)
				this.vArg.reset();
			for(String columnName:h.keySet())
			{
				if(columnName.trim().toUpperCase().equals(this.SortColumnsDesc)||columnName.trim().toUpperCase().equals(this.SortColumns))
					continue;//如果是排序字段，则跳过本次循环
				String columnValue	=	h.get(columnName);
				boolean	firstCondition	=	false;
				
				if(colCount>0)
					firstCondition			=	false;
				else
					firstCondition			=	true;
					//condition	+=	" and ";
				String conditionType			=	this.genConditionType(columnValue, firstCondition);
				condition	+=	conditionType;
				String conditionOperator	=	this.genConditionOperator(columnValue);
				condition	+=	columnName+conditionOperator+" :"+columnName;
				vArg.setArg(columnName, h.get(columnName));
				colCount++;
			}
			if(colCount==0)
				condition="";
		}
		return condition;
	}
	/*入参字段名是否为sort字段名
	 * */
	private boolean isSortColumn(String columnName)
	{
		return columnName.trim().toUpperCase().equals(this.SortColumnsDesc)||columnName.trim().toUpperCase().equals(this.SortColumns);
	}
	public  static boolean isHashEmpty(HashMap h)
	{
		if(h==null || h.isEmpty())
			return true;
		else
			return false;
	}
	/*生成简单sort语句
	 * 
	 * */
	private String genSortForSimple(HashMap<String,String>h)
	{
		String sort="";
		boolean sortDescFlag	=	false;
		if(CmPublic.isHashEmpty(h))
			return "";
		for(String columnName:h.keySet())
		{
			if(columnName.trim().toUpperCase().equals(this.SortColumnsDesc))
			{
				sort	+=h.get(columnName)+",";
				sortDescFlag	=	true;
			}
			else if(columnName.trim().toUpperCase().equals(this.SortColumns))
			{
				sort	+=h.get(columnName)+",";
				
			}
				
		}
		if(CmPublic.isStrNullOrEmpty(sort))
			return "";
		else
		{
			sort=" order by "+this.processCols(sort);
			if(sortDescFlag	)
				sort+=" desc ";
			return sort;
		}
	}
	/*生成多表关联查询时的一条条件语句
	 * 入参：
	 * HashMap<String,String>h：	条件hash,格式为：
	 * {“TA”,TABLEA},{"TB",TABLEB},
	 * 逻辑：
	 * 一个hash通常是三条键值对：一个是TA键值对，一个是TB键值对，一个是关联字段或者字段值键值对
	 * 以形成       TA.CA=TB.CB         这样的sql条件字符串片段  
	 * //TODO 目前只支持等于，这里可扩展操作符，在hash中打入操作符名称，在这里替换操作符，以后支持大于，等于，小于，in not in等这样的操作符
	 * 1、TA,TB必须是上级函数，即genMultiConditionForMultiRS方法入参中ArrayList tableName中的。
	 * 2、如果这个hash中的TB等于ArrayList tableName中的一个tablename，则本次生成条件跳过，直接返回null，以免重复生成条件。
	 * 		因为每个tablename都会遍历所有的关联条件hash
	 * 3、如果hash的key既非TA，又非TB，则表示为一个字段，key为字段名（即CA），value为字段值，字段值既可以是具体的变量值，也可以是关联表字段名，即CB
	 * tableName：表名
	 * 返回：HashMap<String,String>
	 * key=RELATIONTABLE	value=关联表名
	 * key=CONDITION	value=关联条件
	 * */
	private HashMap<String,String> genOneOfMultiConditionForMultiRS(HashMap<String,String>h,String tableName )
	{
		String condition						=	"  ";
		int colCount								=	0;
		String 	TA="";//TABLEA
		String 	TB="";//TABLEB
		String	CA="";//COLUMNA
		String	CB="";//COLUMNB
		if(h==null || h.isEmpty())
			condition =" ";
		else
		{

			for(String columnName:h.keySet())
			{
				if(columnName.trim().toUpperCase().equals("TA"))
					TA=h.get(columnName);
				else if(columnName.trim().toUpperCase().equals("TB"))
				{
					TB=h.get(columnName);
					if(!(TB.trim().toUpperCase().equals(tableName.trim().toUpperCase())))
						return null;
				}
				else
				{
					CA=columnName;
					CB=h.get(columnName);
				}

			}
			if(TB.trim().equals(""))//没有TB，则表示为非表关联，而要打入参数值
			{
				condition	+=	TA+"."+CA+"=:"+CA;
				vArg.setArg(CA,CB);
			}
			else
			{
				condition	+=	TA+"."+CA+"="+TB+"."+CB;
			}
		}
		HashMap<String,String> hCondition =	new HashMap<String,String>();
		hCondition.put(this.KEYForCondition, condition);
		hCondition.put(this.KEYForConditionTable, TA);
		return hCondition;
	}
	/*genCondition(HashMap<String,String>h,boolean flag )
	 * 生成多个条件语句中的一条
	 * 入参：HashMap<String,String>h 条件值对；其中TA表示tablea，TB表示tableb，TA必须有，表示为字段关联，而TB没有则表示为变量
	 * 			
	 * 如果 hash的key数量=1，则 返回的字符串没有and（这个是单个条件所决定，但是多个条件可能多次调用）
	 * 应该是返回的字符串前面都没有and，而只有中间才有and，由调用者自行添加。
	 * */
	private String genOneOfMultiCondition(HashMap<String,String>h )
	{
		String condition						=	"  ";
		int colCount								=	0;
		if(h==null || h.isEmpty())
			condition =" ";
		else
		{
			String 	TA="";
			String 	TB="";
			String	CA="";
			String	CB="";
			for(String columnName:h.keySet())
			{
				if(columnName.trim().toUpperCase().equals("TA"))
					TA=h.get(columnName);
				else if(columnName.trim().toUpperCase().equals("TB"))
					TB=h.get(columnName);
				else
				{
					CA=columnName;
					CB=h.get(columnName);
				}

			}
			if(TB.trim().equals(""))//没有TB，则表示为非表关联，而要打入参数值
			{
				condition	+=	TA+"."+CA+"=:"+CA;
				vArg.setArg(CA,CB);
			}
			else
			{
				condition	+=	TA+"."+CA+"="+TB+"."+CB;
			}
		}
		return condition;
	}
	private String genMultiCondition(ArrayList<HashMap<String,String>>aRelationColumns )
	{
		String	condition="";
		int conditionCount=0;
		for(HashMap<String,String>hRelationColumns:aRelationColumns)
		{
			if(conditionCount>0  )
				condition	+=" and ";//genCondition方法返回的字符串前面都没有and，因此这里处理加上and
			condition	+=	this.genOneOfMultiCondition(hRelationColumns);
			conditionCount++;
		}
		return condition;
	}
	/*
	 * 获取多表关联的查询结果
	 * 入参：ArrayList<String> tablesName						表名列表
	 * 			Connection conn												数据库连接
	 * 			HashMap<String,String>hRelationColumns		关联条件字段（字段a、字段b或者实际值）
	 * */
	//
	public BusiObj getMultiTableData(ArrayList<String> tablesName,Connection conn,ArrayList<HashMap<String,String>>aRelationColumns) throws BaseException
	{
		BusiObj rsBo	=	null;
		String	sql	=	"select ";
		String	columnList=	""; //字段列表
		String	tableList	=	"";
		String	condition="";
		for(String tableName:tablesName)
		{
			String colName=this.generateTableCols(tableName, conn,false);
			columnList +=colName;
			tableList+=tableName+" "+tableName+",";
		}
		this.vArg.reset();
		columnList	=	this.processCols(columnList);//取出字段列表的最后一个逗号
		tableList	=	this.processCols(tableList);
		condition	=	this.genMultiCondition(aRelationColumns);
		sql+=columnList+" from "+tableList +" where "+condition;
		vLogger.info("sql为:\n"+sql );
		rsBo = OperAgent.execQuery(conn, "Query multiTableData " ,sql, vArg);
	    vLogger.info("返回数据是:\n" + BOHelper.boToXML(rsBo));
		return rsBo;
	}
	/*getMultiTableDataForMultiRS  
	 * 进行多表关联查询，获取多表数据形成多个结果集
	 * 入参：ArrayList<String> tablesName 表名数组
	 * 			Connection conn
	 * 			ArrayList<HashMap<String,String>>aRelationColumns  关系字段（字段名、字段值）数组
	 * 返回值：bo
	 * 	里面包括本次查询的信息：有几个表，里面就包含几个单表的bo
	 * 			
	 * */
	//TODO 构造简单多表查询
	//tablesName数组作为外层循环，获取单表的的列名和条件
	//aRelationColumns数组作为条件生成，包括多表关联字段和条件字段及其值
	//aRelationColumns格式为hash，一个hash为一个条件， 
	public BusiObj getMultiTableDataForOneRS(ArrayList<String> tablesName,Connection conn,ArrayList<HashMap<String,String>>aRelationColumns) throws BaseException
	{
		BusiObj rsBo	=	new BusiObj();
		String	sql	=	"select ";
		String	columnList=	""; //字段列表
		String	tableList	=	"";
		String	condition="";
		for(String tableName:tablesName)
		{
			this.vArg.reset();
			String colName=this.generateTableCols(tableName, conn,true);//生成一个表的列名字符串，多张表拼起来
			HashMap<String,String> hCondition = new HashMap<String,String>();
			hCondition	=	this.genMultiConditionForMultiRS(aRelationColumns, tableName);
			if(!(hCondition==null || hCondition.isEmpty()))
			{
				condition = " where "+hCondition.get(this.KEYForCondition);
				tableList	=	hCondition.get(this.KEYForConditionTable);

			}
			else
			{
				condition	=	"";
				tableList	=	tableName;
			}
			columnList	+=colName;
			
			
			vLogger.info("sql为:\n"+sql );
			

		}
		tableList	=	this.processCols(tableList);
		columnList=	this.processCols(columnList);
		sql	=	"select " + columnList + " from " +tableList+" "+condition;
		vLogger.info("总sql为:\n"+sql );
		BusiObj bo = OperAgent.execQuery(conn, "Query getMultiTableDataForOneRS :"+tableList ,sql, vArg);
		//rsBo.setValue("", bo.getBusiObjList("RESULT"));//返回的bo，就是表名，有几个表就有几个bo
		return bo;
	}
	/*getMultiTableDataForMultiRS  
	 * 进行多表关联查询，获取多表数据形成多个结果集
	 * 入参：ArrayList<String> tablesName 表名数组
	 * 			Connection conn
	 * 			ArrayList<HashMap<String,String>>aRelationColumns  关系字段（字段名、字段值）数组
	 * 返回值：bo
	 * 	里面包括本次查询的信息：有几个表，里面就包含几个单表的bo
	 * 			
	 * */
	//TODO 构造简单多表查询
	//tablesName数组作为外层循环，获取单表的的列名和条件
	//aRelationColumns数组作为条件生成，包括多表关联字段和条件字段及其值
	//aRelationColumns格式为hash，一个hash为一个条件， 
	public BusiObj getMultiTableDataForMultiRS(ArrayList<String> tablesName,Connection conn,ArrayList<HashMap<String,String>>aRelationColumns) throws BaseException
	{
		BusiObj rsBo	=	new BusiObj();
		String	sql	=	"select ";
		String	columnList=	""; //字段列表
		String	tableList	=	"";
		String	condition="";
		for(String tableName:tablesName)
		{
			this.vArg.reset();
			String colName=this.generateTableCols(tableName, conn,false);//生成一个表的列名字符串，多张表拼起来
			HashMap<String,String> hCondition = new HashMap<String,String>();
			hCondition	=	this.genMultiConditionForMultiRS(aRelationColumns, tableName);
			if(!(hCondition==null || hCondition.isEmpty()))
			{
				condition = " where "+hCondition.get(this.KEYForCondition);
				tableList	=	hCondition.get(this.KEYForConditionTable);
			}
			else
			{
				condition	=	"";
				tableList	=	tableName;
			}
				
			tableList	=	this.processCols(tableList);
			colName=	this.processCols(colName);
			sql	=	"select " + colName + " from " +tableList+" "+condition;
			vLogger.info("sql为:\n"+sql );
			
			BusiObj bo = OperAgent.execQuery(conn, "Query getMultiTableDataForMultiRS :"+tableName ,sql, vArg);
			rsBo.setValue(tableName, bo.getBusiObjList("RESULT"));//返回的bo，就是表名，有几个表就有几个bo
		}
		return rsBo;
	}
	/*生成多表关联查询时的条件语句
	 * 入参：
	 * ArrayList<HashMap<String,String>>aRelationColumns：	条件数组
	 * tableName：表名
	 * 返回：HashMap<String,String>
	 * key=RELATIONTABLE	value=关联表名
	 * key=CONDITION	value=关联条件
	 * */
	private HashMap<String,String> genMultiConditionForMultiRS(ArrayList<HashMap<String,String>>aRelationColumns ,String tableName)
	{
		String	condition="";
		String	tableList=""+tableName+",";
		int conditionCount=0;
		for(HashMap<String,String>hRelationColumns:aRelationColumns)
		{
			HashMap<String,String> hCondition = new HashMap<String,String>();
			hCondition	=		this.genOneOfMultiConditionForMultiRS(hRelationColumns, tableName);
			if(!(hCondition==null || hCondition.isEmpty()))
			{
				if(conditionCount>0  )
					condition	+=" and ";//genCondition方法返回的字符串前面都没有and，因此这里处理加上and
				condition	+=hCondition.get(this.KEYForCondition);
				String TA	=	hCondition.get(this.KEYForConditionTable)+",";
				if(tableList.toUpperCase().indexOf(TA.toUpperCase())==-1)//TODO 这里可能有问题，index可能不够，
					//因为有可能已有列表里面包含新表名字，但是又非新表的表名，考虑已有列表里面有product_props，新表名为product，就有可能出现问题
					tableList	+=hCondition.get(this.KEYForConditionTable)+",";
				conditionCount++;
			}
			
			
		}
		HashMap<String,String> hCondition = new HashMap<String,String>();
		hCondition.put(this.KEYForConditionTable, tableList);
		hCondition.put(this.KEYForCondition, condition);
		return hCondition;
	}
	/*
	 * 拼出表的列字段字符串，统一每个字段为“表名.列名”，最后都包含一个“,”，由后面统一处理去掉最后一个多余的“,”
	 * 这样是为了处理多表字段方便
	 * */
	private  String generateTableCols(String tableName,Connection conn,boolean flag) throws BaseException
	{
		ResultSet	rs		=	null;
		DatabaseMetaData dbmd=	null;
		String 	tableCols	=	" ";
		String	columnName=	"";
		if(CmPublic.isStrNullOrEmpty(tableName))
		{
			throw new BaseException(vName +"-1000","获取表名为： 信息出错：表名不能为空！");
		}
		String	tabName	=tableName.toUpperCase();//表名统一转换为大写
		try
		{
			dbmd=  conn.getMetaData();   
			rs		=	dbmd.getColumns(null, dbmd.getUserName(), tabName, null);//获取该表的列
		   while   (rs.next()) 
		   {
			   columnName	=	rs.getString(4);
			   tableCols	=	tableCols+tabName+"."+columnName+(flag? " as "+tabName+"_"+columnName:" " )+", ";//拼出表的列字段字符串，最后都包含一个,由后面统一处理
		   }
		}
		catch(SQLException e)
		{
			throw new BaseException(vName +"-008","获取表名为："+tableName+"的列信息出错！");
		}
		finally
		{
	    	try
	    	{		    		
	    		rs.close();
	    		dbmd=null;
	    	}
	    	catch(Exception e){}
		}   
		vLogger.info("表名为" +tabName+"的字段是:\n"+tableCols );
		return tableCols;
	}
	/**getTableData
	 * 获取单表数据，可以加条件，可以排序
	 * 
	 * */
	private BusiObj getTableData(BusiObj bo,String tableName,Connection conn,HashMap<String,String>h ) throws BaseException
	{
		String 	tableCols	=	" ";
		String	columnName=	"";
		String 	sql=" ";
	   ResultSet	rs		=	null;
	   DatabaseMetaData dbmd=	null;
	   String getAllRecordsFlag= ((String) bo.getValue("getAllRecordsFlag", "")).trim();//add at 20101111
	   //生成sql语句，包括字段列表、条件语句、排序语句
	   try
	   {
		   dbmd=  conn.getMetaData();   
		   rs		=	dbmd.getColumns(null, dbmd.getUserName(), tableName.toUpperCase(), null);
		    //要将表名转为大写才能正确取出主键来
		   HashMap<String,String> hColName	=	new HashMap<String,String>();
		   String cName="";
		   String colValue="";
		   this.vArg.reset();
		   while   (rs.next()) 
		   {
			   columnName	=	rs.getString(4).trim();
			   cName=hColName.get(columnName);
			   if(this.isStrNullOrEmpty(cName))
				   hColName.put(columnName, "1");
			   else
				   continue;
			   //add at 20120509 解决日期时间格式问题
			   String columnDataType		=	rs.getString(6);
			   if(columnDataType.trim().toUpperCase().equals("DATE") && !CmPublic.isStrNullOrEmpty(columnName) )
			   {
				   tableCols	=	tableCols+" to_char("+columnName+",'YYYY-MM-DD HH24:MI:SS') "+columnName+", ";
			   }
			   else
				   tableCols	=	tableCols+columnName+", ";
		   }
		   tableCols=this.processCols(tableCols);//字段列表
		   String	condition	=	this.genCondition(h);//条件语句
		   String	sort			=	this.genSortForSimple(h);//sort语句
		   sql=" select   "+tableCols+" from "+tableName+"  "+condition+""+sort;//sql语句
		}
		catch(SQLException e)
		{
			throw new BaseException(vName +"-008","获取表名为："+tableName+"的列信息出错！");
		}
		finally
		{
	    	try
	    	{		    		
	    		rs.close();
	    		dbmd=null;
	    	}
	    	catch(Exception e){}
		}   

    	//vLogger.info("=====================select rows "+tableName+"    sql:" + sql);
    	BusiObj rsBo	= new BusiObj();
    	String	queryName	="Query " +tableName;
		if(getAllRecordsFlag.equals("0"))
		{
			////////////////////////////////////////add at 1104
			if(this.flagQueryCount)
			{
				rsBo = this.getPagingCount(sql, vArg, queryName);
			}
			else
			{
				rsBo = this.getPagingInfo(sql, vArg, queryName);
			}
			////////////////////////////////////////add at 1104
		}
		else
		{
			rsBo = OperAgent.execQuery(conn, queryName,sql, vArg);
		}
    	
    	//vLogger.info("====================getTableData=================返回数据是:\n" + BOHelper.boToXML(rsBo));
    	return rsBo;
	}
	/*
	 * getServiceTableRelation
	 * 获取服务的表关联信息，以提供信息进行服务的多个表之间的参数替换，通常是sequence的替换
	 * */
	public BusiObj getServiceTableRelation(BusiObj inBO)throws BaseException
	{
		Connection	conn	=	inBO.getConnection();
		BusiObj bo =null;
		ArrayList<String> tablesName=new ArrayList<String>();
		tablesName.add("service_tables");
		tablesName.add("service_table_relations");//两张表
		HashMap<String,String>hRelationColumns=new HashMap<String,String>();
		ArrayList<HashMap<String,String>>aRelationColumn =new ArrayList<HashMap<String,String>>();
		hRelationColumns.put("service_table_id", "service_table_id");
		hRelationColumns.put("ta","service_tables");
		hRelationColumns.put("tb","service_table_relations");
		aRelationColumn.add(hRelationColumns);
		HashMap<String,String>hRelationColumns1=new HashMap<String,String>();
		String	service_table_id	=	inBO.getString("service_table_id");
		hRelationColumns1.put("service_table_id", service_table_id);
		hRelationColumns1.put("ta","service_tables");
		aRelationColumn.add(hRelationColumns1);
		bo	=	this.getMultiTableDataForMultiRS(tablesName, conn, aRelationColumn);//获取多表数据形成多个结果集
		return bo;
	}
	/**获取服务涉及的表
	 * 入参：原始服务的bo
	 * 根据原始服务的服务名，获取服务涉及的表
	 * */
	public BusiObj getServiceTables(BusiObj inBO)throws BaseException
	{
		String serviceName	=	inBO.getName();
		
		String tableName		=	"service_tables";
		String	listName		=	"serviceTablesList";
		HashMap<String,String> hCondition=	new 	HashMap<String,String>();
		String	conditionCol=	"SERVICE_NAME";//条件字段名
		hCondition.put(conditionCol,serviceName);//条件字段值
		hCondition.put(this.SortColumns,"SORT_ORDER");//排序字段
		vLogger.info("=================getServiceTables:"+tableName+"\n condition:"+hCondition.toString());
		//获取服务涉及的表名
		return	this.getSimpleTableDataWithCondition(inBO, tableName, hCondition);
	}
	/**多表关联查询的公共方法
	 * 获取服务的表关联关系
	 * 从服务入参包中得到服务名，
	 * */
	public BusiObj commonGetTablesForOneRS(BusiObj inBO)throws BaseException
	{
		Connection conn	=	inBO.getConnection();
		//获取服务涉及的表名，从表：service_tables中获取
		BusiObj		bo	=	this.getServiceTables(inBO);
		ArrayList<BusiObj> boList	=	bo.getBusiObjList(this.QueryResultBoListName);
		BusiObj		retBO	= new BusiObj();
		//获取每个表的关联关系
		for(BusiObj serviceTableBO:boList)
		{
			serviceTableBO.setConnection(conn);
			retBO=	this.getServiceTableRelation(serviceTableBO);//获取表的关联关系，从service_table_relations
		}
		//this.replaceBORowsForMultiTablesUpdate(inBO, boList);
		return this.commonDoRelationTablesForOneRS(retBO, conn);
		//return inBO;
	}
	/**多表关联更新的公共方法
	 * 获取服务的表关联关系
	 * 从服务入参包中得到服务名，
	 * */
	public BusiObj commonSetTablesForMulti(BusiObj inBO)throws BaseException
	{
		Connection conn	=	inBO.getConnection();
		//获取服务涉及的表名，从表：service_tables中获取
		BusiObj		bo	=	this.getServiceTables(inBO);
		ArrayList<BusiObj> boList	=	bo.getBusiObjList(this.QueryResultBoListName);
		this.replaceBORowsForMultiTablesUpdate(inBO, boList);
		return this.commonDoRelationTables(inBO, boList);
		//return inBO;
	}
	/**多表关联查询的公共方法
	 * 获取服务的表关联关系
	 * 从服务入参包中得到服务名，
	 * */
	public BusiObj commonGetTablesForMulti(BusiObj inBO)throws BaseException
	{
		Connection conn	=	inBO.getConnection();
		//获取服务涉及的表名，从表：service_tables中获取
		BusiObj		bo	=	this.getServiceTables(inBO);
		ArrayList<BusiObj> boList	=	bo.getBusiObjList(this.QueryResultBoListName);
		return this.commonDoRelationTables(inBO, boList);
		//return inBO;
	}
	/**替换多表更新的bo数据关联记录
	 * 获取服务的表关联关系
	 * 从服务入参包中得到服务名，
	 * */
	public BusiObj replaceBORowsForMultiTablesUpdate(BusiObj inBO,ArrayList<BusiObj> boList)throws BaseException
	{
		Connection conn	=	inBO.getConnection();
		//获取每个表的关联关系
		for(BusiObj serviceTableBO:boList)
		{
			serviceTableBO.setConnection(conn);
			String	service_table_name	=	serviceTableBO.getString("service_table_name");//源表名
			String 	column_sequnce=serviceTableBO.getString("COLUMN_SEQUNCE");//源表字段的sequence
			String 	column_name=serviceTableBO.getString("COLUMN_NAME");//源表字段
			String 	col_order_no=serviceTableBO.getString("COL_ORDER_NO");//订单号字段 
			boolean orderNoFlag	=	!CmPublic.isStrNullOrEmpty(col_order_no);//col_order_no不空，则表示有订单号字段
			String order_no	=	"";

			ArrayList<BusiObj> boSrcList	=	inBO.getBusiObjList(service_table_name);//源表原记录
			BusiObj		retBO=	this.getServiceTableRelation(serviceTableBO);//获取表的关联关系，从service_table_relations
			//TODO 此地方可优化，减少db交互
			ArrayList<BusiObj> boRelationList	=	retBO.getBusiObjList("service_table_relations");
			for(BusiObj boSrc:boSrcList)
			{
				String srcColValue=boSrc.getString(column_name,"");
				if(CmPublic.isStrNullOrEmpty(srcColValue))
					continue;
				String srcColOrderNoValue="";
				boolean replaceOrderNoFlag=false;
				if(orderNoFlag)//有订单号字段，则生成订单号
				{
					srcColOrderNoValue=boSrc.getString(col_order_no,"");//modified at 20120523
					if(srcColOrderNoValue.trim().indexOf("-")==0)//为负数，订单号字段，则生成订单号
					{
						order_no =	this.genSequenceForOrder(col_order_no, conn);
						replaceOrderNoFlag=true;
					}
					else
						order_no=srcColOrderNoValue;// add at 20110330 ，这句话是保证外部带了订单号，也能保存，以免订单号不能保存外部订单号。
				}
				
				if(srcColValue.trim().indexOf("-")==0)//为负数，生成序列，并替换之
				{

					String seq	=	this.genSequence(column_sequnce,conn);//生成序列；
					//替换
					for(BusiObj boRelation:boRelationList)//关联表替换
					{
						String relation_table_name=boRelation.getString("RELATION_TABLE_NAME");//关联表名
						String relation_column_name=boRelation.getString("RELATION_COLUMN_NAME");//关联字段名
						
						String gen_relation_column_method=boRelation.getString("GEN_RELATION_COLUMN_METHOD");//关联字段的生成方法
						ArrayList<BusiObj> boRelationTableList	=	inBO.getBusiObjList(relation_table_name);//源表原记录
						for(BusiObj boRelationTable:boRelationTableList)
						{
							String relationColValue=boRelationTable.getString(relation_column_name);
							if(relationColValue.trim().equals(srcColValue))//替换之
							{
								boRelationTable.setString(relation_column_name, seq);//更新源表记录,字段名可与关联表字段不一样.
							}
							vLogger.info("================= to update relaitionOrderNo column:"+relation_column_name+"\n col_order_no:"+col_order_no);
							if(orderNoFlag&&relation_column_name.trim().toUpperCase().equals(col_order_no.trim().toUpperCase()))//有订单号字段，并且关联字段等于订单号字段，则需要考虑替换
							{

								String relationOrderNo=boRelationTable.getString(col_order_no,"");
								vLogger.info("=================relaitionOrderNo column:"+relation_column_name+"\n col_order_no:"
										+col_order_no+"\nreplaceOrderNoFlag:"+replaceOrderNoFlag
										+"\nrelationOrderNo:"+relationOrderNo
										+"\nsrcColOrderNoValue:"+srcColOrderNoValue);
								if(replaceOrderNoFlag &&
										relationColValue.equals(srcColOrderNoValue))//源订单号值等于关联订单号值，即负数
								{
									vLogger.info("=================update relaitionOrderNo column:"+relation_column_name+"\n order_no:"+order_no);
									boRelationTable.setString(relation_column_name, order_no);//更新源表记录,字段名可与关联表字段不一样.
								}
							}
						}
					}
					//关联表替换完成后，替换源表数据
					//没有关联关系，则只替换自身
					boSrc.setString(column_name,seq);
					if(orderNoFlag)
						boSrc.setString(col_order_no,order_no);
				}
			}
			vLogger.info("========================返回数据是:\n" + BOHelper.boToXML(inBO));
		}
		return inBO;
	}
	private BusiObj commonDoRelationTables(BusiObj inBO,ArrayList<BusiObj> boList)throws BaseException
	{
		BusiObj		retBO	=	new BusiObj();
		staffNo	=inBO.getString("SESSION_STAFF_NO", "");//获取整个入参包的staff_no
		for(BusiObj serviceTableBO:boList)
		{
			serviceTableBO.setConnection(inBO.getConnection());
			
			String	service_table_name	=	serviceTableBO.getString("service_table_name");//源表名
			String	service_name =serviceTableBO.getString("SERVICE_NAME_SINGLE");//源表服务名
			String	bo_list_name =serviceTableBO.getString("BO_LIST_NAME");//源表服务名
			BusiObj		boCall	=	new BusiObj();
			boCall.setValue(bo_list_name, inBO.getBusiObjList(service_table_name));//TODO
			//boCall.setString("NEW_FLAG", "1");
			//boCall = inBO.getBusiObj(service_table_name);
			
			boCall.setConnection(inBO.getConnection());
			boCall.setName(service_name);
			boCall.setString("SESSION_STAFF_NO", staffNo);//多个结果集只要设置一次staffno，其余的在这里一次性指定
			BusiObj 	dataBO						=		ServiceBus.runService(boCall);  
			retBO.setValue(service_table_name, dataBO);
		}
		retBO.setValue("ResultFlag", "0");
		return retBO;
	}
	private BusiObj commonDoRelationTablesForOneRS(BusiObj inBO,Connection conn)throws BaseException
	{
		
		ArrayList<BusiObj> tablesBoList =inBO.getBusiObjList("SERVICE_TABLES");
		ArrayList<BusiObj> relationBoList =inBO.getBusiObjList("SERVICE_TABLE_RELATIONS");
		ArrayList<HashMap<String,String>>a1RelationColumn =new ArrayList<HashMap<String,String>>();
		ArrayList<String> aTablesName = new ArrayList<String> ();
		for(BusiObj tableBo:tablesBoList)
		{
			
			
			String service_table_id = tableBo.getString("SERVICE_TABLE_ID");
			String service_table_name	=	tableBo.getString("SERVICE_TABLE_NAME");
			String column_name	=	tableBo.getString("COLUMN_NAME");
			aTablesName.add(service_table_name);
			for(BusiObj relationBo:relationBoList)
			{
				String service_table_id_relation = relationBo.getString("SERVICE_TABLE_ID");
				if(service_table_id_relation.toUpperCase().equals(service_table_id.toUpperCase()))
				{
					HashMap<String,String>h1RelationColumns=new HashMap<String,String>();
					String relation_table_name	=	relationBo.getString("RELATION_TABLE_NAME");
					aTablesName.add(relation_table_name);
					String relation_column_name	=	relationBo.getString("RELATION_COLUMN_NAME");
					h1RelationColumns.put("TA", service_table_name);
					h1RelationColumns.put("TB", relation_table_name);
					h1RelationColumns.put(column_name, relation_column_name);
					a1RelationColumn.add(h1RelationColumns);
				}
			}
		}
		BusiObj		retBO	=	new BusiObj();
		retBO=this.getMultiTableDataForOneRS(aTablesName, conn, a1RelationColumn);
		
		
		
		retBO.setValue("ResultFlag", "0");
		return retBO;
	}
	private boolean isValidSequenceName(String seqName,Connection conn)throws BaseException
	{
		boolean flag=false;
		String	sql=	"select count(1) from user_sequences where SEQUENCE_NAME=:SEQUENCE_NAME ";
		vArg.reset();
		vArg.setArg("SEQUENCE_NAME", seqName);
		int cnt	=	OperAgent.QueryInt(conn, "Query seqName", sql, vArg);
		if(cnt>0)
			flag = true;
		return flag;
	}
	/**动态建立sequence
	 * 
	 * */
	private void createSequence(String seqName,Connection conn)throws BaseException
	{
		String sql="create sequence "+seqName+" minvalue 1 maxvalue 999999999 start with 1 increment by 1 cache 3";
		vArg.reset();
		OperAgent.execUpdate(conn, "Create seqName", sql, vArg);
	}
	private String replaceStr(String str,String split)
	{
		String strPattern	=	"["+split+"]";
		Pattern pattern = Pattern.compile(strPattern);
		Matcher matcher = pattern.matcher(str);
		String retStr	=	matcher.replaceAll("");
		return retStr;
	}
	/**生成sequence name
	 *如果不存在则创建该sequence 
	 * */
	private String genPKSequenceName(String seqType,Connection conn)throws BaseException
	{
		if (CmPublic.isStrNullOrEmpty(seqType))
			throw new BaseException(vName +"-0022","序列名不能为空！");
		
		String seqName=seqType.trim().toUpperCase()+"_SEQ";
		if(!this.isValidSequenceName(seqName, conn))
			this.createSequence(seqName, conn);
		return seqName;
	}
	/**生成sequence name,字段名+日期
	 *如果不存在则创建该sequence 
	 * */
	private String genSequenceName(String seqType,Connection conn)throws BaseException
	{
		if (CmPublic.isStrNullOrEmpty(seqType))
			throw new BaseException(vName +"-0012","序列名不能为空！");
		
		String seqName=seqType.trim().toUpperCase()+this.replaceStr(CmPublic.getToday(),"-");
		if(!this.isValidSequenceName(seqName, conn))
			this.createSequence(seqName, conn);
		return seqName;
	}
	/**生成 主键序号
	 * pkColumn
	 * */
	public String genSequencePK(String pkColumn,Connection conn)throws BaseException
	{
		String seq_name	=	this.genPKSequenceName(pkColumn, conn);
		String seq				=	this.genSequence(seq_name, conn);
		return seq;
	}
	/**生成订单号，工单号
	 * seqType：order_no，work_order_no
	 * */
	public String genSequenceForOrder(String seqType,Connection conn)throws BaseException
	{
		String seq_name	=	this.genSequenceName(seqType, conn);
		String seq=	 this.genSequence(seq_name, conn);
		seq =	this.replaceStr(CmPublic.getToday(),"-")+this.genStringForLength(seq, this.orderSeqLength);
		return seq;
	}
	/**genStringForLength：数字字符串根据长度补零
	 * 
	 * */
	private String genStringForLength(String str,int length)throws BaseException
	{
		String src	=	str.trim();
		long l=Integer.parseInt(src);
		int strLength	=	src.length();
		if(strLength>length)
			throw new BaseException(vName +"-0013","字符串"+str+"的长度大于指定长度"+length+"！");
		String format = "%0"+length+"d";
		String target=String.format(format, l);
		return target;
	}
	private String genSequence(String seq_name,Connection conn)throws BaseException
	{
		String seq="";
		
		String	sql	=	"select "+seq_name+".nextval from dual";
		vArg.reset();
		seq	=	OperAgent.QueryString(conn, "get sequence "+seq_name, sql, vArg);
		return seq;
	}
	/**获取单表所有数据，无条件
	 * */
	public BusiObj  getSimpleTableData(BusiObj bo,String tableName)throws BaseException
	{
		Connection conn	=	bo.getConnection();
		return this.getTableData(bo, tableName, conn,null);
	}
	/**根据条件获取单表数据
	 * 支持条件、排序
	 * */
	public BusiObj  getSimpleTableDataWithCondition(BusiObj bo,String tableName,HashMap<String,String>h)throws BaseException
	{
		Connection conn	=	bo.getConnection();
		return this.getTableData(bo, tableName, conn,h);
	}
	/**插入一行数据
	 * */
	private void insertOneRow(BusiObj bo,String tableName,Connection conn ) throws BaseException
	{
		String 	tableCols	=	" ",tableColsVar="";
		String	columnName=	"",columnDataType="";
		String 	sql=" ";
		//sql					=	" Insert Into  "+tab_name+" ($columns) Values ($columns) ";
	   ResultSet	rs		=	null;
	   DatabaseMetaData dbmd=	null;
	   int  colCount=0;
	   HashMap<String,Object> hRowData		=	bo.getRawData();
	   try
	   {
		   dbmd=  conn.getMetaData();   
		   rs		=	dbmd.getColumns(null, dbmd.getUserName(), tableName.toUpperCase(), null);
		    //要将表名转为大写才能正确取出主键来
		   HashMap<String,String> hColName	=	new HashMap<String,String>();
		   String colValue="";
		   this.vArg.reset();
		   while   (rs.next()) 
		   {
			   
			   columnName	=	rs.getString(4).trim();
			   String cName	=	hColName.get(columnName);
			   /////////////////////20120322突然取dbmd.getColumns得到重复列。。。。
			   if(this.isStrNullOrEmpty(cName))
				   hColName.put(columnName, "1");
			   else
				   continue;
			   /////////////////////
			   columnDataType=	rs.getString(6);
			   tableCols	=	tableCols+columnName+", ";
			   //tableColsVar	=	tableColsVar+":"+columnName+", ";
			   colValue	=	(String)hRowData.get(columnName);
			   ////////////////////增加没有打入值的处理，由于之前pk已经生成，并且pk肯定不能为null，因此，null值的就默认为空串
/*			   if(colValue==null)
			   {
			      int nullable = rs.getInt("NULLABLE");
			      if (nullable == DatabaseMetaData.columnNullable) {
			    	  colValue="";
			      } else {
			    	 // colValue="";
			      }
			   }*/
			   ////////////////////
			   if(columnDataType.equals("DATE") && !CmPublic.isStrNullOrEmpty(colValue) )
				   tableColsVar	=	tableColsVar+"TO_DATE("+":"+columnName+",'YYYY-MM-DD HH24:MI:SS')"+", ";
			   else
				   tableColsVar	=	tableColsVar+":"+columnName+", ";
				   //colValue=	"TO_DATE('"+colValue+"','YYYY-MM-DD HH24:MI:SS')";
				   
			   vArg.setArg(columnName, colValue);
		   }
		   tableCols=this.processCols(tableCols);
		   tableColsVar=this.processCols(tableColsVar);
		   sql=" Insert Into  "+tableName+" ( "+tableCols+") Values ("+tableColsVar+") ";
		}
		catch(SQLException e)
		{
			throw new BaseException(vName +"-009","获取表名为："+tableName+"列信息出错！");
		}
		finally
		{
	    	try
	    	{		    		
	    		rs.close();
	    		dbmd=null;
	    	}
	    	catch(Exception e){}
		}   

    	vLogger.info("=====================insert row "+tableName+"    sql:" + sql);
    	OperAgent.execUpdate(conn, "insert  "+tableName,sql, vArg);
	}
	/*删除字段列表等后面多余的","
	 * */
	private String processCols(String cols)
	{
		String ret_value		=	"";
		int	beginIndex		=	0;	
		String	str				=	cols.trim();
		ret_value					=	str.substring(beginIndex, str.length()-1);
		return ret_value;
	}
	private void genPK(BusiObj curRecordBO,ArrayList<String>	aTableKey,Connection conn) throws BaseException
	{
	       String 	 	curKeyValue		=	"";  
	       String 		seq					=	""; 
		   for(String curKey:aTableKey)
		   {
			   curKeyValue					=	curRecordBO.getString(curKey);
			   if(Integer.parseInt(curKeyValue)<=0)
			   {
				   //get sequence
				   seq = this.genSequencePK(curKey, conn);
				   curRecordBO.setString(curKey, seq);
			   }
		   }
	}
	/**新增、删除、修改（先删后插）单表数据，必须是有主键的表
	 * 入参：
	 * 						bo：		记录bo，
	 *  			listName：	记录集名称
	 *  			tabName：	表名
	 *  HashMap<String,String>hCol	：		需要替换值的字段名+字段值（前台无法确定的值，比如staff字段等）
	 * */
	public void setTableData(BusiObj bo,String listName,String tabName,HashMap<String,String>hCol ) throws BaseException
	{
		ArrayList<BusiObj> member = bo.getBusiObjList(listName);
		Connection	conn					=	bo.getConnection();
		String newFlag 						= "";//bo.getString("new_flag"); //0:不重新插入记录。//1：重新插入记录
		String	tableName				=	tabName.trim().toUpperCase();//统一转化为大写
		ArrayList<String>	aTableKey	=	this.GetTableKey(tableName,conn);//得到主键
		if(aTableKey.size()<1)
			throw new BaseException(vName +"-007","表名为："+tableName+"没有定义主键，不能使用方法setTableData进行数据处理！");
		boolean flagReplaceRow=false;
		if(hCol !=null) flagReplaceRow=true;
		for (BusiObj m : member) 
		{
			this.deleteOneRow(m, tableName,conn,aTableKey);
			newFlag 						= m.getString("new_flag","");
			if (newFlag.equals("1")) 
			{
				if(flagReplaceRow)	CmPublic.replaceRowColumn(m, hCol);
				this.genPK(m, aTableKey, conn);//生成主键，如果为负数
				this.insertOneRow(m, tableName,conn);//,aTableKey
			}
		}
	}
	/**获取一个表的主键字段
	 * 入参：
	 * tableName：表名
	 * conn：数据库连接
	 * 出参：
	 * 返回 ArrayList<String>
	 * */
	private ArrayList<String> GetTableKey(String tableName,Connection conn) throws BaseException
	 {
		   ArrayList<String>	aTableKey	=	new	ArrayList<String>();
		   ResultSet	rs		=	null;
		   DatabaseMetaData dbmd=	null;
		   String 	columnName;
		   int		key_col		=	0;
		   try
		   {
			   dbmd=  conn.getMetaData();   
			   rs = dbmd.getPrimaryKeys(null,dbmd.getUserName(),tableName.toUpperCase());    //要将表名转为大写才能正确取出主键来
			   while   (rs.next()) 
			   {
				   key_col++;
				   columnName	=	rs.getString(4);
				   //System.out.println("表名为："+tab_name+"的主键"+key_col+",列名为："+columnName);
				   aTableKey.add(columnName);
			   }
			}
			catch(SQLException e)
			{
				throw new BaseException(vName +"-006","获取表名为："+tableName+"的主键出错！");
			}
			finally
			{
		    	try
		    	{		    		
		    		rs.close();
		    		dbmd=null;
		    	}
		    	catch(Exception e){}
			}   

			return		aTableKey;
	 }
	/**
	 * 根据主键获取sql条件语句，并且设置参数集，主要用于删除记录
	 * */
   private String getSqlPkCondition(BusiObj curRecordBO,ArrayList<String> aTableKey) throws BaseException
   {
	   //String	CurKey	=	"";//,sql_where_CurKey="  "CurKey_Value	=	"",
	   int		iCount			=	aTableKey.size();
	   String	where_value[]	=	new String[iCount];
	   HashMap	hCurKey_Sql_Where=	new HashMap();
	   this.vArg.reset();
	   String curKeyValue	=	"";
	   String sqlPkCondition=	"";
	   int i=0;
	   for(String curKey:aTableKey)
	   {
		   curKeyValue					=	curRecordBO.getString(curKey);
		   sqlPkCondition				=	sqlPkCondition+" "+curKey+" = :"+curKey;
		   where_value[i]				=	curKeyValue;
		   this.vArg.setArg(curKey, curKeyValue);
		   if(i<iCount-1)
			   sqlPkCondition	=	sqlPkCondition+" and ";
		   i++;
		   
	   }
	   vLogger.info("sqlPkCondition is :\n"+sqlPkCondition+"\n this.vArg="+this.vArg.toString());
	   return sqlPkCondition;
/*	   for(int i=0;i<iCount;i++)
	   {
		   CurKey				=	(String)aTableKey.get(i);
		   CurKey_Value			=	curRecordBO.getString(CurKey);
		   sql_where_CurKey		=	sql_where_CurKey+" "+CurKey+" = :"+CurKey;
		   where_value[i]		=	CurKey_Value;
		   if(i<iCount-1)
			   sql_where_CurKey	=	sql_where_CurKey+" and ";
		   
	   }*/
//	   hCurKey_Sql_Where.put("SQL_WHERE", sqlPkCondition);
//	   //hCurKey_Sql_Where.put("SQL_PARA", where_value);
//	   return hCurKey_Sql_Where;
   }
	public static String getstackmsg(Throwable e)
	{
		String msg = "";
		StringWriter sw = null; 
		PrintWriter pw = null;
		try
		{
			sw = new StringWriter();
			pw = new PrintWriter(sw);
			e.printStackTrace(pw);
			sw.flush();
			msg = sw.toString();
			sw.close();
			pw.close();
		}
		catch(Throwable te) {}
		finally
		{
			if(pw != null) try{ pw.close(); } catch(Throwable te){}
			if(sw != null) try{ sw.close(); } catch(Throwable te){}
		}
		
        return msg;
    }
	/**获取bo的记录数
	 * */
	public static int getCountForBOQuery(BusiObj bo) throws BaseException
	{
		int ret		=	bo.getBusiObjList("RESULT").size();
		return ret;
	}
	//add at 20120529 删除公共字段中的生失效时间，如果bo中已存在，不过这个更新暂时不做，可能
	public HashMap<String,String>removeCols(HashMap<String,String> h,BusiObj bo,String listName) throws BaseException
	{
		HashMap<String,String>hCol	=	new HashMap<String,String>();
		ArrayList<BusiObj> member = bo.getBusiObjList(listName);
		for(BusiObj m:member)
		{
			for(String key:h.keySet())
			{
				vLogger.info("key="+key+";bo key value="+ m.getString(key,"")+";gen key value="+"key="+h.get(key));
				if(!CmPublic.isStrNullOrEmpty(key))
				{
					String	colName	=	key.toLowerCase().trim();
					if((colName.equals("eff_date")
							||colName.equals("exp_date")
							||colName.equals("create_time"))
							&& !CmPublic.isStrNullOrEmpty(m.getString(key,"")))
						continue;
				}

		/*		if(CmPublic.isStrNullOrEmpty(bo.getString("eff_date",""))
						||CmPublic.isStrNullOrEmpty(bo.getString("exp_date",""))
						||CmPublic.isStrNullOrEmpty(bo.getString("create_time",""))
						)*/
					
				hCol.put(key, h.get(key));
			}
		}
		
		return hCol;
	}
	/**
	 * 生成公用的oper_time=sysdate，eff_date=1970-1-1，exp_date=2099-12-31字段信息
	 * */
	public HashMap<String,String>genCommonCols() throws BaseException
	{
		HashMap<String,String> h=	new HashMap<String,String>();
		return this.genCommonCols(h);
	}
	private HashMap<String,String>genCommonCols(HashMap<String,String> h) throws BaseException
	{
		HashMap<String,String>hCol	=	new HashMap<String,String>();
		String	oper_time		=	CmPublic.getSysdate();	
		String	eff_date		=	CmPublic.getSysdate();//CmPublic.genEffExpDate(1)
		String	exp_date		=	CmPublic.genEffExpDate(2);
		hCol.put("oper_time", oper_time);
		hCol.put("eff_date", eff_date);
		hCol.put("exp_date", exp_date);
		hCol.put("state", "1");
		hCol.put("operator", staffNo);
	/*			hCol.put("operator", "1");
		hCol.put("notes", "1");*/
		
		for(String key:h.keySet())
		{
			hCol.put(key, h.get(key));
		}
		return hCol;
	}
	/*输入格式为：1=eff_date 1970 1 1;2=exp_date 2099 12 31
	 * 输出格式为
	*/
	public static String genEffExpDate(int mode)throws BaseException
	{
		long lDate=0;
		if(mode==1)
		{
			
		}
		else if(mode==2)
		{
			lDate	=	4102358400000l;//2099-12-31 08:00:00
		}
		else
			return CmPublic.getSysdate();
		SimpleDateFormat sDateFormat = new SimpleDateFormat(gDateFormat);
		String str_send_date = sDateFormat.format(new java.util.Date(lDate));
		return str_send_date;
	}
	public static String getFirstDayOfMonth(){
		String str = "";
		SimpleDateFormat sdf=new SimpleDateFormat(gDateFormat);
		Calendar lastDate = Calendar.getInstance();
		lastDate.set(Calendar.DATE,1);//设为当前月的1号
		str=sdf.format(lastDate.getTime());
		return str;
	}
	public static String getSysdate()throws BaseException
	{
		return CmPublic.genDateTimeFormat(gDateFormat);
	}
	public static String getToday()throws BaseException
	{
		return CmPublic.genDateTimeFormat(gDayFormat);
	}
	public static String genDateTimeFormat(String format)throws BaseException
	{
		SimpleDateFormat sDateFormat = new SimpleDateFormat(format);
		String str_send_date = sDateFormat.format(new java.util.Date());
		return str_send_date;
	}
	public static void replaceBOColumn(BusiObj bo,HashMap<String,String>hCol,String boName)throws BaseException
	{
		ArrayList<BusiObj> boList	=bo.getBusiObjList(boName);	
		for(String colName:hCol.keySet())
		{
			String colValue = hCol.get(colName);
			
			for(BusiObj row:boList)
			{
				row.setString(colName, colValue);
			}
		}
	}
	//TODO
	public void replaceRowPK(BusiObj bo)throws BaseException
	{
		BusiObj mdBO	=	bo.getBusiObj(CmPublic.MDListName);
		BusiObj 	mdTableBO=	mdBO.getBusiObj(CmPublic.MDTableListName);
		BusiObj 	mdRelationBO=	mdBO.getBusiObj(CmPublic.MDRelationListName);
		
		ArrayList<BusiObj> mdTableBOList	=bo.getBusiObjList(CmPublic.MDTableListName);//获取每个表的记录
		ArrayList<BusiObj> mdRelationBOList	=mdBO.getBusiObjList(CmPublic.MDRelationListName);	//获取关联关系记录
		for (BusiObj m : mdTableBOList) 
		{
			String tableName	=	m.getString(CmPublic.MDTN);
			ArrayList<BusiObj> tableBOList	=	bo.getBusiObjList(tableName);
			for (BusiObj row : tableBOList)
			{
				String	createSeqFlag	=	row.getString("create_seq_flag","0");
				if(createSeqFlag.trim().equals("1"))//更新相关的seq
				{
					//createSeq();//seq名称必须为表名+pk字段名
					//replaceSeqRelation();
				}
			}
			
		}
		//ArrayList<BusiObj> mdBOList	=bo.getBusiObjList();	
	}
	/*替换一个bo中所有记录的中某些字段值（使用相同的值替换，比如后台自动添加每行记录的staff）
	 * 主要是一些后台确定的字段
	 * 
	 * 入参：
	 * 	bo：需要替换值的bo
	 * HashMap<String,String>hCol  需要替换值的字段名+字段值（前台无法确定的值，比如staff字段等）
	 * */
	public static void replaceRowColumn(BusiObj row,HashMap<String,String>hCol)throws BaseException
	{
		for(String colName:hCol.keySet())
		{
			String colValue = hCol.get(colName);
			if(CmPublic.isStrNullOrEmpty(colValue))
				colValue="";
			row.setString(colName, colValue);
		}
	}
	public static boolean isStrNullOrEmpty(String str)
	{
		boolean flag =false;
		if(str ==null || str.trim().equals(""))
			flag=true;
		return flag;
	}
	//分页

	public void setPageRows(int rows)
	{
		pageRows	=	rows;
	}
	public String getCountSql(String sql)throws BaseException 
	{
		return "select count(1) totalRows,ceil(count(1)/:pageRows) pageCount from ("+sql+" )";
	}

	public BusiObj getPagingCount(String srcSql,SQLArgs sqlArgs,String queryName)throws BaseException
	{
		String Sql	=	this.getCountSql(srcSql);
		sqlArgs.setArg("pageRows", pageRows);
		this.vLogger.info("getPagingCount:------"+Sql);
		return OperAgent.execQuery(vConn, queryName, Sql, sqlArgs);
	}
	
	public String getPagingSql(String srcSql)throws BaseException 
	{
		return "select * from ( " +
				" select aaa.*,rownum rn from " +
				" ("+srcSql+") aaa " +
				"  where rownum <= :endRowNum " +
				"  ) bbb " +
				" where rn >:startRowNum";
	}
	public BusiObj getPagingInfo(String srcSql,SQLArgs sqlArgs,String queryName)throws BaseException
	{
		String Sql	=	this.getPagingSql(srcSql);
		sqlArgs.setArg("endRowNum", endRowNum);
		sqlArgs.setArg("startRowNum", startRowNum);
		return OperAgent.execQuery(vConn, queryName, Sql, sqlArgs);
	}
	public void setPageRowNum(int curPageNo)throws BaseException
	{
		if(curPageNo<1) curPageNo	=	1;
		startRowNum	=	(curPageNo-1)*pageRows;
		endRowNum	=	(curPageNo)*pageRows;
		//return OperAgent.execQuery(vConn, queryName, gSql, vArg);
	}
	/*删除sql in条件等后面多余的","
	 * */
	public String processArgs(String args)
	{
		String ret_value		=	"";
		int	beginIndex		=	0;	
		String	str				=	args.trim();
		ret_value					=	str.substring(beginIndex, str.length()-1);
		return ret_value;
	}
	//输出方法的结果信息到log4j
	public void logFuncRsOutput(String funcName,BusiObj bo ) throws BaseException
	{
		vLogger.info(funcName+":::返回数据是:\n\n\n\n" + BOHelper.boToXML(bo));
	}
	public HashMap<String,String> transferHashKey2Uppercase(HashMap<String,String>hSource)throws BaseException
	{
		HashMap<String,String> hTarget	=	new	HashMap<String,String>();
		for(String key:hSource.keySet())
		{
			hTarget.put(key.toUpperCase(), hSource.get(key));
			
		}
		return hTarget;
		
	}
	public void execInsertSqlByHash(Connection conn,String tableName,HashMap<String,String> hColDataType,HashMap<String,String> hColValue)throws BaseException
	{
		this.vArg.reset();
		HashMap<String,String> hUVColDataType	=	this.transferHashKey2Uppercase(hColDataType);
		HashMap<String,String> hUVColValue	=	this.transferHashKey2Uppercase(hColValue);
		String tableCols="",colValue="",tableColsVar="";
		for(String colName:hUVColValue.keySet())
		{
			String colDataType =hUVColDataType.get(colName);
			//只有data类型才需要在hColName中设置值
			//从hColValue中获取列名和列值
			tableCols	=	tableCols+colName+", ";
			colValue	=	(String)hUVColValue.get(colName);
			if( !CmPublic.isStrNullOrEmpty(colDataType) && colDataType.toUpperCase().equals("DATE"))
				   tableColsVar	=	tableColsVar+"TO_DATE("+":"+colName+",'YYYY-MM-DD HH24:MI:SS')"+", ";
			else
				   tableColsVar	=	tableColsVar+":"+colName+", ";
			vArg.setArg(colName, colValue);
		}
		tableCols=this.processCols(tableCols);
		tableColsVar=this.processCols(tableColsVar);
		String sql=" Insert Into  "+tableName+" ( "+tableCols+") Values ("+tableColsVar+") ";
		vLogger.info("=====================insert row "+tableName+"    sql:" + sql);
	    OperAgent.execUpdate(conn, "insert  "+tableName,sql, vArg);
	}
	//add at 20120831 判断是否手机号码
	public static boolean isMobileNum(String mobile_num)throws BaseException
	{
		boolean flag=	false;
		
		flag 		= isStrNullOrEmpty(mobile_num);
		
		if(flag) return flag;
		//如果非空则flag=false
		mobile_num	=	mobile_num.trim();
		int iLen	=	mobile_num.length();
		if(mobile_num.startsWith("1")&& (iLen == 11 ))
			flag	=	true;
		else
			flag	=	false;
		return flag;
	}
}
